﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;
using HRM.DAL.Report;

namespace HRM.DAL.Gateway
{
    public class EmployeeGateway : DBGateway
    {
        public int GetEmployeeID(Code empCode)
        {
            int empId = 0;
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT tblEmployees.EmployeeId FROM tblCode INNER JOIN tblEmployees ON tblCode.Id = tblEmployees.Code_CodeID where tblCode.CodeName='" + empCode.CodeName + "'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    empId = Convert.ToInt32(reader[0]);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
            return empId;
        }

        public bool SaveEmployee(int codeId, Employee employee)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Insert into tblEmployees values(@Name,@FathersName,@MothersName,@Sex,@Blood,@Email,@DOB,@JoiningDate," +
                                                       "@ContractFinishDate,@Education,@Resume,@Skill,@Nationality,@NID,@Status,@CatagoryID,@ProjectID,@DesignationID,@Code_CodeID)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@Name", SqlDbType.VarChar).Value = employee.Name;
                CommandObj.Parameters.Add("@FathersName", SqlDbType.VarChar).Value = employee.FathersName;
                CommandObj.Parameters.Add("@MothersName", SqlDbType.VarChar).Value = employee.MothersName;
                CommandObj.Parameters.Add("@Sex", SqlDbType.VarChar).Value = employee.Sex;
                CommandObj.Parameters.Add("@Blood", SqlDbType.VarChar).Value = employee.Blood;
                CommandObj.Parameters.Add("@Email", SqlDbType.VarChar).Value = employee.Email;
                CommandObj.Parameters.Add("@DOB", SqlDbType.DateTime).Value = employee.DOB;
                CommandObj.Parameters.Add("@JoiningDate", SqlDbType.DateTime).Value = employee.JoiningDate;
                CommandObj.Parameters.Add("@ContractFinishDate", SqlDbType.DateTime).Value = employee.ContractFinishDate;
                CommandObj.Parameters.Add("@Education", SqlDbType.VarChar).Value = employee.Education;
                CommandObj.Parameters.Add("@Resume", SqlDbType.VarChar).Value = employee.Resume;
                CommandObj.Parameters.Add("@Skill", SqlDbType.VarChar).Value = employee.Skill;
                CommandObj.Parameters.Add("@Nationality", SqlDbType.VarChar).Value = employee.Nationality;
                CommandObj.Parameters.Add("@NID", SqlDbType.VarChar).Value = employee.NID;
                CommandObj.Parameters.Add("@Status", SqlDbType.VarChar).Value = employee.Status;
                CommandObj.Parameters.Add("@CatagoryID", SqlDbType.Int).Value = employee.EmpCatagoryId.Id;
                CommandObj.Parameters.Add("@ProjectID", SqlDbType.Int).Value = employee.Project.Id;
                CommandObj.Parameters.Add("@DesignationID", SqlDbType.Int).Value = employee.Designation.Id;
                CommandObj.Parameters.Add("@Code_CodeID", SqlDbType.Int).Value = codeId;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Saving Employee Information, Try Check Inputs & Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool DeleteEmployee(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"delete from tblEmployees where EmployeeId='" + id + "'";
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Deleting Employee");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }


        public bool EditEmployee(int id, int codeId, Employee employee)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblEmployees set Name=@Name,FathersName=@FathersName,MothersName=@MothersName,Sex=@Sex,Blood=@Blood,Email=@Email,DOB=@DOB,JoiningDate=@JoiningDate," +
                                    "ContractFinishDate=@ContractFinishDate,Education=@Education,Resume=@Resume,Skill=@Skill,Nationality=@Nationality,NID=@NID,Status=@Status,CatagoryID=@CatagoryID," +
                                    "ProjectID=@ProjectID,DesignationID=@DesignationID,Code_CodeID=@Code_CodeID where EmployeeId='" + id + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@Name", SqlDbType.VarChar).Value = employee.Name;
                CommandObj.Parameters.Add("@FathersName", SqlDbType.VarChar).Value = employee.FathersName;
                CommandObj.Parameters.Add("@MothersName", SqlDbType.VarChar).Value = employee.MothersName;
                CommandObj.Parameters.Add("@Sex", SqlDbType.VarChar).Value = employee.Sex;
                CommandObj.Parameters.Add("@Blood", SqlDbType.VarChar).Value = employee.Blood;
                CommandObj.Parameters.Add("@Email", SqlDbType.VarChar).Value = employee.Email;
                CommandObj.Parameters.Add("@DOB", SqlDbType.DateTime).Value = employee.DOB;
                CommandObj.Parameters.Add("@JoiningDate", SqlDbType.DateTime).Value = employee.JoiningDate;
                CommandObj.Parameters.Add("@ContractFinishDate", SqlDbType.DateTime).Value = employee.ContractFinishDate;
                CommandObj.Parameters.Add("@Education", SqlDbType.VarChar).Value = employee.Education;
                CommandObj.Parameters.Add("@Resume", SqlDbType.VarChar).Value = employee.Resume;
                CommandObj.Parameters.Add("@Skill", SqlDbType.VarChar).Value = employee.Skill;
                CommandObj.Parameters.Add("@Nationality", SqlDbType.VarChar).Value = employee.Nationality;
                CommandObj.Parameters.Add("@NID", SqlDbType.VarChar).Value = employee.NID;
                CommandObj.Parameters.Add("@Status", SqlDbType.VarChar).Value = employee.Status;
                CommandObj.Parameters.Add("@CatagoryID", SqlDbType.Int).Value = employee.EmpCatagoryId.Id;
                CommandObj.Parameters.Add("@ProjectID", SqlDbType.Int).Value = employee.Project.Id;
                CommandObj.Parameters.Add("@DesignationID", SqlDbType.Int).Value = employee.Designation.Id;
                CommandObj.Parameters.Add("@Code_CodeID", SqlDbType.Int).Value = codeId;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Saving Employee Information, Try Check Inputs & Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public ViewEmployee SearchEmployeeByCode(int codeId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT distinct(tblCode.CodeName),desig.Title,emp.EmployeeId, emp.Name, emp.FathersName, emp.MothersName,emp.Sex, emp.Blood, emp.Email, emp.DOB, emp.JoiningDate, 
                emp.ContractFinishDate, emp.Education, emp.Resume, emp.Skill, emp.Nationality,emp.NID, emp.Status, tblPhotos.FileName, con.FirstContact, con.Secondcontact, 
                con.EmergencyContactPerson, con.EmergencyContactNumber, con.PresentAddress, con.PermanentAddress,tblProjects.ProjectName, tblCatagories.EmpCatagory
                FROM tblCode FULL OUTER JOIN tblEmployees as emp ON tblCode.Id = emp.Code_CodeID FULL OUTER JOIN tblDesignations as desig on emp.DesignationID=desig.Id FULL OUTER JOIN
                tblPhotos ON emp.EmployeeId = tblPhotos.EmployeeID FULL OUTER JOIN tblContacts as con ON emp.EmployeeId = con.EmployeeID FULL OUTER JOIN tblProjects ON emp.ProjectID = tblProjects.ProjectID FULL OUTER JOIN
                tblCatagories ON emp.CatagoryID = tblCatagories.CatagoryID FULL OUTER JOIN tblCertificate ON emp.EmployeeId = tblCertificate.EmpID 
                where (tblCode.Id = '" + codeId + "')";
                SqlDataReader reader = CommandObj.ExecuteReader();
                ViewEmployee viewEmployee = new ViewEmployee();
                while (reader.Read())
                {
                    Code code=new Code();
                    Designation designation=new Designation();
                    Employee employee=new Employee();
                    Photo photo=new Photo();
                    Contact contact=new Contact();
                    Project project=new Project();
                    Catagory catagory=new Catagory();
                    Certificate certificate=new Certificate();

                    //Loading Object
                    code.CodeName = reader[0].ToString();
                    designation.Title = reader[1].ToString();

                    employee.Id = Convert.ToInt32(reader[2]);
                    employee.Name = reader[3].ToString();
                    employee.FathersName = reader[4].ToString();
                    employee.MothersName = reader[5].ToString();
                    employee.Sex = reader[6].ToString();
                    employee.Blood = reader[7].ToString();
                    employee.Email = reader[8].ToString();
                    employee.DOB = Convert.ToDateTime(reader[9]);
                    employee.JoiningDate = Convert.ToDateTime(reader[10]);
                    employee.ContractFinishDate = Convert.ToDateTime(reader[11]);
                    employee.Education = reader[12].ToString();
                    employee.Resume = reader[13].ToString();
                    employee.Skill = reader[14].ToString();
                    employee.Nationality = reader[15].ToString();
                    employee.NID = reader[16].ToString();
                    employee.Status = reader[17].ToString();

                    photo.FileName = reader[18].ToString();

                    contact.FirstContact = reader[19].ToString();
                    contact.Secondcontact = reader[20].ToString();
                    contact.EmergencyContactPerson = reader[21].ToString();
                    contact.EmergencyContactNumber = reader[22].ToString();
                    contact.PresentAddress = reader[23].ToString();
                    contact.PermanentAddress = reader[24].ToString();

                    project.ProjectName = reader[25].ToString();

                    catagory.EmpCatagory = reader[26].ToString();

                   // certificate.Exam = reader[27].ToString();
                   // certificate.FileName = reader[28].ToString();

                    viewEmployee.Code = code;
                    viewEmployee.Designation = designation;
                    viewEmployee.Employee = employee;
                    viewEmployee.Photo = photo;
                    viewEmployee.Project = project;
                    viewEmployee.Catagory = catagory;
                   // viewEmployee.Certificate = certificate;
                    viewEmployee.Contact = contact;
                }
                return viewEmployee;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured During Reading Employee Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<ViewEmployee> SearchEmployeeByProject(Project aProject)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT  distinct(tblCode.CodeName),desig.Title,emp.EmployeeId, emp.Name, emp.FathersName, emp.MothersName,emp.Sex, emp.Blood, emp.Email, emp.DOB, emp.JoiningDate, 
                emp.ContractFinishDate, emp.Education, emp.Resume, emp.Skill, emp.Nationality,emp.NID, emp.Status, tblPhotos.FileName, con.FirstContact, con.Secondcontact, 
                con.EmergencyContactPerson, con.EmergencyContactNumber, con.PresentAddress, con.PermanentAddress,tblProjects.ProjectName, tblCatagories.EmpCatagory
                FROM tblCode FULL OUTER JOIN tblEmployees as emp ON tblCode.Id = emp.Code_CodeID FULL OUTER JOIN tblDesignations as desig on emp.DesignationID=desig.Id FULL OUTER JOIN
                tblPhotos ON emp.EmployeeId = tblPhotos.EmployeeID FULL OUTER JOIN tblContacts as con ON emp.EmployeeId = con.EmployeeID FULL OUTER JOIN tblProjects ON emp.ProjectID = tblProjects.ProjectID FULL OUTER JOIN
                tblCatagories ON emp.CatagoryID = tblCatagories.CatagoryID FULL OUTER JOIN tblCertificate ON emp.EmployeeId = tblCertificate.EmpID 
                where (tblProjects.ProjectID = '" + aProject.Id + "')";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<ViewEmployee>viewEmployees=new List<ViewEmployee>();
                while (reader.Read())
                {
                    ViewEmployee viewEmployee = new ViewEmployee();
                    Code code = new Code();
                    Designation designation = new Designation();
                    Employee employee = new Employee();
                    Photo photo = new Photo();
                    Contact contact = new Contact();
                    Project project = new Project();
                    Catagory catagory = new Catagory();
                    Certificate certificate = new Certificate();

                    //Loading Object
                    code.CodeName = reader[0].ToString();
                    designation.Title = reader[1].ToString();
                    if(reader[2].ToString()!="")
                    {
                        employee.Id = Convert.ToInt32(reader[2]);
                        employee.DOB = Convert.ToDateTime(reader[9]);
                        employee.JoiningDate = Convert.ToDateTime(reader[10]);
                        employee.ContractFinishDate = Convert.ToDateTime(reader[11]);
                    }
                    employee.Name = reader[3].ToString();
                    employee.FathersName = reader[4].ToString();
                    employee.MothersName = reader[5].ToString();
                    employee.Sex = reader[6].ToString();
                    employee.Blood = reader[7].ToString();
                    employee.Email = reader[8].ToString();
                    
                    employee.Education = reader[12].ToString();
                    employee.Resume = reader[13].ToString();
                    employee.Skill = reader[14].ToString();
                    employee.Nationality = reader[15].ToString();
                    employee.NID = reader[16].ToString();
                    employee.Status = reader[17].ToString();

                    photo.FileName = reader[18].ToString();

                    contact.FirstContact = reader[19].ToString();
                    contact.Secondcontact = reader[20].ToString();
                    contact.EmergencyContactPerson = reader[21].ToString();
                    contact.EmergencyContactNumber = reader[22].ToString();
                    contact.PresentAddress = reader[23].ToString();
                    contact.PermanentAddress = reader[24].ToString();

                    project.ProjectName = reader[25].ToString();

                    catagory.EmpCatagory = reader[26].ToString();

                  //  certificate.Exam = reader[27].ToString();
                    //certificate.FileName = reader[28].ToString();

                    viewEmployee.Code = code;
                    viewEmployee.Designation = designation;
                    viewEmployee.Employee = employee;
                    viewEmployee.Photo = photo;
                    viewEmployee.Project = project;
                    viewEmployee.Catagory = catagory;
                   // viewEmployee.Certificate = certificate;
                    viewEmployee.Contact = contact;

                    viewEmployees.Add(viewEmployee);
                }
                return viewEmployees;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured During Reading Employee Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<ViewEmployee> SearchEmployeeByStatus(string Status)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT  distinct(tblCode.CodeName),desig.Title,emp.EmployeeId, emp.Name, emp.FathersName, emp.MothersName,emp.Sex, emp.Blood, emp.Email, emp.DOB, emp.JoiningDate, 
                emp.ContractFinishDate, emp.Education, emp.Resume, emp.Skill, emp.Nationality,emp.NID, emp.Status, tblPhotos.FileName, con.FirstContact, con.Secondcontact, 
                con.EmergencyContactPerson, con.EmergencyContactNumber, con.PresentAddress, con.PermanentAddress,tblProjects.ProjectName, tblCatagories.EmpCatagory
                FROM tblCode FULL OUTER JOIN tblEmployees as emp ON tblCode.Id = emp.Code_CodeID FULL OUTER JOIN tblDesignations as desig on emp.DesignationID=desig.Id FULL OUTER JOIN
                tblPhotos ON emp.EmployeeId = tblPhotos.EmployeeID FULL OUTER JOIN tblContacts as con ON emp.EmployeeId = con.EmployeeID FULL OUTER JOIN tblProjects ON emp.ProjectID = tblProjects.ProjectID FULL OUTER JOIN
                tblCatagories ON emp.CatagoryID = tblCatagories.CatagoryID FULL OUTER JOIN tblCertificate ON emp.EmployeeId = tblCertificate.EmpID 
                where (emp.Status='" + Status + "')";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<ViewEmployee> viewEmployees = new List<ViewEmployee>();
                while (reader.Read())
                {
                    ViewEmployee viewEmployee = new ViewEmployee();
                    Code code = new Code();
                    Designation designation = new Designation();
                    Employee employee = new Employee();
                    Photo photo = new Photo();
                    Contact contact = new Contact();
                    Project project = new Project();
                    Catagory catagory = new Catagory();
                    Certificate certificate = new Certificate();

                    //Loading Object
                    code.CodeName = reader[0].ToString();
                    designation.Title = reader[1].ToString();

                    employee.Id = Convert.ToInt32(reader[2]);
                    employee.Name = reader[3].ToString();
                    employee.FathersName = reader[4].ToString();
                    employee.MothersName = reader[5].ToString();
                    employee.Sex = reader[6].ToString();
                    employee.Blood = reader[7].ToString();
                    employee.Email = reader[8].ToString();
                    employee.DOB = Convert.ToDateTime(reader[9]);
                    employee.JoiningDate = Convert.ToDateTime(reader[10]);
                    employee.ContractFinishDate = Convert.ToDateTime(reader[11]);
                    employee.Education = reader[12].ToString();
                    employee.Resume = reader[13].ToString();
                    employee.Skill = reader[14].ToString();
                    employee.Nationality = reader[15].ToString();
                    employee.NID = reader[16].ToString();
                    employee.Status = reader[17].ToString();

                    photo.FileName = reader[18].ToString();

                    contact.FirstContact = reader[19].ToString();
                    contact.Secondcontact = reader[20].ToString();
                    contact.EmergencyContactPerson = reader[21].ToString();
                    contact.EmergencyContactNumber = reader[22].ToString();
                    contact.PresentAddress = reader[23].ToString();
                    contact.PermanentAddress = reader[24].ToString();

                    project.ProjectName = reader[25].ToString();

                    catagory.EmpCatagory = reader[26].ToString();

                   // certificate.Exam = reader[27].ToString();
                  //  certificate.FileName = reader[28].ToString();

                    viewEmployee.Code = code;
                    viewEmployee.Designation = designation;
                    viewEmployee.Employee = employee;
                    viewEmployee.Photo = photo;
                    viewEmployee.Project = project;
                    viewEmployee.Catagory = catagory;
                  //  viewEmployee.Certificate = certificate;
                    viewEmployee.Contact = contact;

                    viewEmployees.Add(viewEmployee);
                }
                return viewEmployees;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured During Reading Employee Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        } 

        public List<ViewEmployee> SearchEmployeeByCatagory(Catagory aCatagory)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT  distinct(tblCode.CodeName),desig.Title,emp.EmployeeId, emp.Name, emp.FathersName, emp.MothersName,emp.Sex, emp.Blood, emp.Email, emp.DOB, emp.JoiningDate, 
                emp.ContractFinishDate, emp.Education, emp.Resume, emp.Skill, emp.Nationality,emp.NID, emp.Status, tblPhotos.FileName, con.FirstContact, con.Secondcontact, 
                con.EmergencyContactPerson, con.EmergencyContactNumber, con.PresentAddress, con.PermanentAddress,tblProjects.ProjectName, tblCatagories.EmpCatagory
                
                FROM tblCode FULL OUTER JOIN tblEmployees as emp ON tblCode.Id = emp.Code_CodeID FULL OUTER JOIN tblDesignations as desig on emp.DesignationID=desig.Id FULL OUTER JOIN
                tblPhotos ON emp.EmployeeId = tblPhotos.EmployeeID FULL OUTER JOIN tblContacts as con ON emp.EmployeeId = con.EmployeeID FULL OUTER JOIN tblProjects ON emp.ProjectID = tblProjects.ProjectID FULL OUTER JOIN
                tblCatagories ON emp.CatagoryID = tblCatagories.CatagoryID FULL OUTER JOIN tblCertificate ON emp.EmployeeId = tblCertificate.EmpID 
                where (emp.CatagoryID='" + aCatagory.Id + "')";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<ViewEmployee> viewEmployees = new List<ViewEmployee>();
                while (reader.Read())
                {
                    ViewEmployee viewEmployee = new ViewEmployee();
                    Code code = new Code();
                    Designation designation = new Designation();
                    Employee employee = new Employee();
                    Photo photo = new Photo();
                    Contact contact = new Contact();
                    Project project = new Project();
                    Catagory catagory = new Catagory();
                    Certificate certificate = new Certificate();

                    //Loading Object
                    code.CodeName = reader[0].ToString();
                    designation.Title = reader[1].ToString();

                    employee.Id = Convert.ToInt32(reader[2]);
                    employee.Name = reader[3].ToString();
                    employee.FathersName = reader[4].ToString();
                    employee.MothersName = reader[5].ToString();
                    employee.Sex = reader[6].ToString();
                    employee.Blood = reader[7].ToString();
                    employee.Email = reader[8].ToString();
                    employee.DOB = Convert.ToDateTime(reader[9]);
                    employee.JoiningDate = Convert.ToDateTime(reader[10]);
                    employee.ContractFinishDate = Convert.ToDateTime(reader[11]);
                    employee.Education = reader[12].ToString();
                    employee.Resume = reader[13].ToString();
                    employee.Skill = reader[14].ToString();
                    employee.Nationality = reader[15].ToString();
                    employee.NID = reader[16].ToString();
                    employee.Status = reader[17].ToString();

                    photo.FileName = reader[18].ToString();

                    contact.FirstContact = reader[19].ToString();
                    contact.Secondcontact = reader[20].ToString();
                    contact.EmergencyContactPerson = reader[21].ToString();
                    contact.EmergencyContactNumber = reader[22].ToString();
                    contact.PresentAddress = reader[23].ToString();
                    contact.PermanentAddress = reader[24].ToString();

                    project.ProjectName = reader[25].ToString();

                    catagory.EmpCatagory = reader[26].ToString();

                   // certificate.Exam = reader[27].ToString();
                  //  certificate.FileName = reader[28].ToString();

                    viewEmployee.Code = code;
                    viewEmployee.Designation = designation;
                    viewEmployee.Employee = employee;
                    viewEmployee.Photo = photo;
                    viewEmployee.Project = project;
                    viewEmployee.Catagory = catagory;
                   // viewEmployee.Certificate = certificate;
                    viewEmployee.Contact = contact;

                    viewEmployees.Add(viewEmployee);
                }
                return viewEmployees;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured During Reading Employee Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public string GetEmployeeName(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select Name from tblEmployees where EmployeeId='"+id+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                string name = null;
                while (reader.Read())
                {
                    name = reader[0].ToString();
                }
                return name;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool UpdateDesignation(int empId, int presentDesigId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblEmployees set DesignationID=@DesignationID where EmployeeId='" + empId + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@DesignationID", SqlDbType.Int).Value = presentDesigId;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Saving Employee Information, Try Check Inputs & Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool ChangeStatus(int empId, string status)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblEmployees set Status=@Status where EmployeeId='" + empId + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@Status", SqlDbType.VarChar).Value = status;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Employee Status!");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}